package cn.tips.dmp.supports.excel;


import lombok.Data;
import cn.tips.dmp.supports.status.ComConstant;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @program: tips_back_end
 * @description:
 * @author: kzl
 * @create: 2020-07-03 14:39
 **/
@Data
public class ExportExcel {

    /**显示的导出表的标题**/
    private String title;
    //导出表的列名--常规列名
    private String[] rowsName;
    //导出表的列名--基础
    private List<String> rowsBasicName;
    //导出表的列名--集合
    private List<String> rowsListName;

    private List<Object[]> dataList = new ArrayList<Object[]>();

    HttpServletResponse response;

    public ExportExcel() {
    }

    //构造方法，传入要导出的数据
    public ExportExcel(String title, List<String> rowsBasicName, List<String> rowsListName, List<Object[]> dataList) {
        this.title = title;
        this.rowsBasicName = rowsBasicName;
        this.rowsListName = rowsListName;
        this.dataList = dataList;
    }


    public void setRowCellValue(HSSFRow row, HSSFCellStyle style, int location, String value){
        HSSFCell cell = row.createCell(location, CellType.NUMERIC);
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }

    /**
     * 列头单元格样式
     **/
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //字体加粗
        font.setBold(true);
//        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return style;

    }

    /**
     * 列数据信息单元格样式
      **/
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(IndexedColors.BLACK.index);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(IndexedColors.BLACK.index);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor(IndexedColors.BLACK.index);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(IndexedColors.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        return style;
    }

    // 生成多个动态sheet，和sheet内部多个内容
    public void dynamicExport(HSSFWorkbook workbook, HSSFCellStyle columnTopStyle, HSSFCellStyle cellStyle, int sheetNum, String sheetTitle, List<ExportExcel> result)throws IOException {
        HSSFSheet sheet = workbook.createSheet();
        // 设置默认列宽，width为字符个数,10个字符换一次行
        sheet.setDefaultColumnWidth(20);
        sheet.setColumnWidth(0, 9 * 256);
        workbook.setSheetName(sheetNum, sheetTitle);

        File tempFile = File.createTempFile("temp","test.xls");
        //自适应列宽度
//        sheet.autoSizeColumn(1);
//        sheet.autoSizeColumn(1, true);
        //统计已经生成多少行
        int createdRowNum = 0;
        try {
            //循环生成表格
            if(!CollectionUtils.isEmpty(result)){
                for (int index = 0; index < result.size(); index++) {
                    String excelTitle = result.get(index).getTitle();
                    String[] headerRownames = result.get(index).getRowsName();
                    List<Object[]> dataResultList = result.get(index).getDataList();

                    // 产生表格标题行
                    HSSFRow rowm = sheet.createRow(createdRowNum);
                    HSSFCell cellTiltle = rowm.createCell(0);
                    //设置高度
                    rowm.setHeight((short) (25 * 35));
                    //合并单元格  起始行, 终止行, 起始列, 终止列
                    sheet.addMergedRegion(new CellRangeAddress(createdRowNum, createdRowNum, 0, (headerRownames.length-1)));

                    cellTiltle.setCellStyle(columnTopStyle);
                    cellTiltle.setCellValue(excelTitle);
                    //已生成行数+1
                    createdRowNum++;

                    // 定义所需列数
                    int columnNum = headerRownames.length;
                    // 在索引2的位置创建行(最顶端的行开始的第二行)
                    HSSFRow rowRowName = sheet.createRow(createdRowNum);
                    //设置高度
//                    rowRowName.setHeight((short) (25 * 25));
                    // 将列头设置到sheet的单元格中
                    for (int n = 0; n < columnNum; n++) {
                        //创建列头对应个数的单元格
                        HSSFCell cellRowName = rowRowName.createCell(n);
                        //设置列头单元格的数据类型
                        cellRowName.setCellType(CellType.STRING);
                        //设置列头单元格的值
                        cellRowName.setCellValue(new HSSFRichTextString(headerRownames[n]));
                        //设置列头单元格样式
                        cellRowName.setCellStyle(columnTopStyle);
                    }
                    //已生成行数+1
                    createdRowNum++;
                    //将查询出的数据设置到sheet对应的单元格中
                    for (int i = 0; i < dataResultList.size(); i++) {
                        //遍历每个对象
                        Object[] obj = dataResultList.get(i);
                        //创建所需的行数
                        HSSFRow row = sheet.createRow(createdRowNum + i);
                        //取出第二个列（内容最长）的那列
                        int tVal = obj[1].toString().length();
                        //商+余数
                        int quotient  = tVal/10;
                        int remainder = tVal%10 > 0 ? 1 : 0;
                        int res = quotient + remainder;
                        //设置高度
                        row.setHeight((short) (25 * 20 * res - res*100));
                        for (int j = 0; j < obj.length; j++) {
                            //设置单元格的数据类型
                            HSSFCell cell = row.createCell(j, CellType.STRING);
                            //设置单元格的值
                            cell.setCellValue(obj[j] != null ? obj[j]+"" : "");
                            //设置单元格样式
                            cell.setCellStyle(cellStyle);
                        }
                    }
                    //已生成行数+已生成表格行数-- 计算下一个表格起始行数-
                    createdRowNum +=dataResultList.size();
//                    //让列宽随着导出的列长自动适应
//                    for (int colNum = 0; colNum < columnNum; colNum++) {
//                        int columnWidth = sheet.getColumnWidth(colNum) / 256;
//                        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
//                            HSSFRow currentRow;
//                            //当前行未被使用过
//                            if (sheet.getRow(rowNum) == null) {
//                                currentRow = sheet.createRow(rowNum);
//                            } else {
//                                currentRow = sheet.getRow(rowNum);
//                            }
//                            if (currentRow.getCell(colNum) != null) {
//                                //取得当前的单元格
//                                HSSFCell currentCell = currentRow.getCell(colNum);
//                                int length = 0;
//                                //如果当前单元格类型为字符串
//                                if (currentCell.getCellType() == CellType.STRING) {
//                                    try {
//                                        length = currentCell.getStringCellValue().getBytes().length;
//                                    } catch (Exception e) {
//                                        e.printStackTrace();
//                                    }
//                                    if (columnWidth < length) {
//                                        //将单元格里面值大小作为列宽度
//                                        columnWidth = length;
//                                    }
//                                }
//                            }
//                        }
//                        if (colNum == 0) {
//                            sheet.setColumnWidth(colNum, (columnWidth - 2) * 128);
//                        } else {
//                            sheet.setColumnWidth(colNum, columnWidth * 256);
//                        }
//                    }
                    //已生成行数+1--新的表格和上面的表格空一行
                    createdRowNum++;
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // 一个excel 生成sheet
    public void excelExport(HSSFWorkbook workbook, HSSFCellStyle columnTopStyle, HSSFCellStyle cellStyle, int sheetNum, String sheetTitle, ExportExcel result)throws IOException {
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);

        File tempFile = File.createTempFile("temp","test.xls");
        // 设置默认列宽，width为字符个数,10个字符换一次行
        sheet.setDefaultColumnWidth(16);
        sheet.setColumnWidth(0, 9 * 256);
        sheet.setColumnWidth(1, 9 * 256 * 8);
        //自适应列宽度
//        sheet.autoSizeColumn(1);
//        sheet.autoSizeColumn(1, true);
        //统计已经生成多少行
        int createdRowNum = 0;
        try {
            String excelTitle = result.getTitle();
            String[] headerRownames = result.getRowsName();
            List<Object[]> dataResultList = result.getDataList();

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(createdRowNum);
            HSSFCell cellTiltle = rowm.createCell(0);
            //设置高度
            rowm.setHeight((short) (25 * 35));
            //合并单元格  起始行, 终止行, 起始列, 终止列
            sheet.addMergedRegion(new CellRangeAddress(createdRowNum, createdRowNum, 0, (headerRownames.length-1)));

            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(excelTitle);
            //已生成行数+1
            createdRowNum++;

            // 定义所需列数
            int columnNum = headerRownames.length;
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            HSSFRow rowRowName = sheet.createRow(createdRowNum);
            //设置高度
            rowRowName.setHeight((short) (25 * 25));
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                //创建列头对应个数的单元格
                HSSFCell cellRowName = rowRowName.createCell(n);
                //设置列头单元格的数据类型
                cellRowName.setCellType(CellType.STRING);
                //设置列头单元格的值
                cellRowName.setCellValue(new HSSFRichTextString(headerRownames[n]));
                //设置列头单元格样式
                cellRowName.setCellStyle(columnTopStyle);
            }
            //已生成行数+1
            createdRowNum++;
            //将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataResultList.size(); i++) {
                //遍历每个对象
                Object[] obj = dataResultList.get(i);
                //创建所需的行数
                HSSFRow row = sheet.createRow(createdRowNum + i);
                //设置高度
                row.setHeight((short) (25 * 20));
                for (int j = 0; j < obj.length; j++) {
                    //设置单元格的数据类型
                    HSSFCell cell = row.createCell(j, CellType.STRING);
                    //设置单元格的值
                    cell.setCellValue(obj[j] != null ? obj[j]+"" : "");
                    //设置单元格样式
                    cell.setCellStyle(cellStyle);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void setCellValue(HSSFRow row, HSSFCellStyle style, int columnIndex, String value){
        //设置单元格的数据类型
        HSSFCell cell =  row.createCell(columnIndex, CellType.STRING);
        //设置单元格的值
        cell.setCellValue(value);
        //设置单元格样式
        cell.setCellStyle(style);

    }

    public static String getCellValue(Cell cell){
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case 0: // 数字
                //short s = cell.getCellStyle().getDataFormat();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
//                    SimpleDateFormat sdf = null;
                    // 验证short值
//                    if (cell.getCellStyle().getDataFormat() == 14) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
//                    } else if (cell.getCellStyle().getDataFormat() == 21) {
//                        sdf = new SimpleDateFormat("HH:mm:ss");
//                    } else if (cell.getCellStyle().getDataFormat() == 22) {
//                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
//                    } else {
//                        throw new RuntimeException("日期格式错误!!!");
//                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                } else if(cell.getCellStyle().getDataFormat() == 58) {
                    Date d = cell.getDateCellValue();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = formatter.format(d);
                }else{
                    // 取得当前Cell的数值
                    cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                }
                break;
            case 1: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case 2: // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case 3: // 空值
                cellValue = null;
                break;
            case 4: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case 5: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static void main(String[] args) {

        String psContent = "批示\n";
//        psContent = psContent.substring(0, psContent.length()-1);
        System.out.println(psContent + "------");
    }
}

